package OSdesign.Function_show;

import java.sql.SQLException;

public class InquireManager {
    public InquireManager(){
        //构造函数
    }
    public Object[][] gradeInquire(){
        int i = 0,count = 0;
        MySQL_Conn mysql = new MySQL_Conn();
        mysql.Connect();
        String sql = "select * from grade";
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try{
            while(true){
                if(!mysql.set.next())  break;
                count++;
            }
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
        Object[][] objects= new Object[count][6];
        System.out.println(count);
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try {
            while(true){
                if(!mysql.set.next()) break;
                System.out.println("正在逐条执行SQL语句");
                objects[i][0] = mysql.set.getString("pno");
                objects[i][1] = mysql.set.getString("mno");
                objects[i][2] = mysql.set.getString("team");
                objects[i][3] = mysql.set.getString("score");
                objects[i][4] = mysql.set.getString("medal");
                objects[i][5] = mysql.set.getString("total");
                i++;
            }
            System.out.println("逐条执行完毕");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return objects;
    }

    public Object[][] teamScoreInquire(){
        int i = 0,count = 0,ranking = 1;
        MySQL_Conn mysql = new MySQL_Conn();
        mysql.Connect();
        String sql = "select team,totals from prize order by totals desc";
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try{
            while(true){
                if(!mysql.set.next())  break;
                count++;
            }
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
        Object[][] objects= new Object[count][3];
        System.out.println(count);
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try {
            while(true){
                if(!mysql.set.next()) break;
                System.out.println("正在逐条执行SQL语句");
                objects[i][0] = ranking+"";
                objects[i][1] = mysql.set.getString("team");
                objects[i][2] = mysql.set.getString("totals");
                i++; ranking++;
            }
            System.out.println("逐条执行完毕");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return objects;
    }

    public Object[][] breakRecordInquire(){
        int i = 0,count = 0,ranking = 1;
        MySQL_Conn mysql = new MySQL_Conn();
        mysql.Connect();
        String sql = "select player.pno,pname,competition.mname,mrecord from competition,player,grade " +
                "where competition.mname = player.mname and player.pno = grade.pno and mrecord=score;";
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try{
            while(true){
                if(!mysql.set.next())  break;
                count++;
            }
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
        Object[][] objects= new Object[count][5];
        System.out.println(count);
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try {
            while(true){
                if(!mysql.set.next()) break;
                System.out.println("正在逐条执行SQL语句");
                objects[i][0] = ranking+"";
                objects[i][1] = mysql.set.getString("player.pno");
                objects[i][2] = mysql.set.getString("pname");
                objects[i][3] = mysql.set.getString("competition.mname");
                objects[i][4] = mysql.set.getString("mrecord");
                i++; ranking++;
            }
            System.out.println("逐条执行完毕");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return objects;
    }

    public Object[][] gradeAsk(String str){
        int i = 0,count = 0;
        MySQL_Conn mysql = new MySQL_Conn();
        mysql.Connect();
        String sql = "select * from grade where pno ='"+str+"'";
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try{
            while(true){
                if(!mysql.set.next())  break;
                count++;
            }
        }catch(SQLException sqle){
            sqle.printStackTrace();
        }
        Object[][] objects= new Object[count][6];
        System.out.println(count);
        try{
            mysql.set = mysql.stmt.executeQuery(sql);
        } catch(SQLException sqle){
            sqle.printStackTrace();
        }
        try {
            while(true){
                if(!mysql.set.next()) break;
                System.out.println("正在逐条执行SQL语句");
                objects[i][0] = mysql.set.getString("pno");
                objects[i][1] = mysql.set.getString("mno");
                objects[i][2] = mysql.set.getString("team");
                objects[i][3] = mysql.set.getString("score");
                objects[i][4] = mysql.set.getString("medal");
                objects[i][5] = mysql.set.getString("total");
                i++;
            }
            System.out.println("逐条执行完毕");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return objects;
    }
}
